package com.library.dao;

import com.library.util.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    private final QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());

    // 书籍热度排行榜
    public List<Object[]> getBookHotRank() {
        String sql = "SELECT bk.ISBN, bk.bname, COUNT(br.serNum) AS borrow_count " +
                "FROM bookcopy bc JOIN borrowrec br ON bc.barCode = br.barCode " +
                "JOIN book bk ON bc.ISBN = bk.ISBN " +
                "GROUP BY bk.ISBN, bk.bname ORDER BY borrow_count DESC LIMIT 5";
        try {
            List<Object[]> result = new ArrayList<>();
            List<Object[]> raw = queryRunner.query(sql, new ArrayListHandler());
            if (raw != null) result.addAll(raw);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    // 查询所有已上架书籍及其未注销副本信息，可多条件模糊查询
    public List<Object[]> queryBooks(String author, String isbn, String publisher, String bname) {
        StringBuilder sql = new StringBuilder("SELECT bk.ISBN, bk.bname, bk.author, bk.publisher, bk.introduction, bk.pubDate, bc.barCode, bc.place, bc.status " +
                "FROM book bk JOIN bookcopy bc ON bk.ISBN = bc.ISBN WHERE bk.bookStatus = 1 AND bc.status != 2");
        List<String> params = new ArrayList<>();
        if (author != null && !author.trim().isEmpty()) {
            sql.append(" AND bk.author LIKE ?");
            params.add("%" + author + "%");
        }
        if (isbn != null && !isbn.trim().isEmpty()) {
            sql.append(" AND bk.ISBN LIKE ?");
            params.add("%" + isbn + "%");
        }
        if (publisher != null && !publisher.trim().isEmpty()) {
            sql.append(" AND bk.publisher LIKE ?");
            params.add("%" + publisher + "%");
        }
        if (bname != null && !bname.trim().isEmpty()) {
            sql.append(" AND bk.bname LIKE ?");
            params.add("%" + bname + "%");
        }
        try {
            List<Object[]> result = new ArrayList<>();
            List<Object[]> raw = queryRunner.query(sql.toString(), new ArrayListHandler(), params.toArray());
            if (raw != null) result.addAll(raw);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 通过条码号获取书名
     */
    public String getNameByBarCode(String barCode) {
        String sql = "select b.bname from bookcopy bc left join book b on bc.ISBN = b.ISBN where bc.barCode = ?";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<String>(), barCode);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 分页获取书籍信息
     * @param pageNum 页码（从1开始）
     * @param pageSize 每页显示数量
     * @return 当前页的书籍列表
     */
    public java.util.List<com.library.entity.Book> listBookByPage(int pageNum, int pageSize) {
        int startIndex = (pageNum - 1) * pageSize;
        String sql = "select * from book limit ?, ?";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Book.class), startIndex, pageSize);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    /**
     * 获取书籍总数
     * @return 书籍总数
     */
    public Long getTotalCount() {
        String sql = "select count(*) from book";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>());
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    /**
     * 根据条件搜索书籍并分页
     * @param keyword 搜索关键词（书名、作者或ISBN）
     * @param pageNum 页码
     * @param pageSize 每页显示数量
     * @return 符合条件的书籍列表
     */
    public java.util.List<com.library.entity.Book> pageBooks(String keyword, int pageNum, int pageSize) {
        int startIndex = (pageNum - 1) * pageSize;
        String sql = "select * from book where bname like ? or author like ? or ISBN like ? limit ?, ?";
        try {
            String searchPattern = "%" + keyword + "%";
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.BeanListHandler<>(com.library.entity.Book.class),
                    searchPattern, searchPattern, searchPattern, startIndex, pageSize);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return new java.util.ArrayList<>();
    }

    /**
     * 获取搜索结果的总数
     * @param keyword 搜索关键词
     * @return 符合条件的书籍总数
     */
    public Long getTotalCountByKeyword(String keyword) {
        String sql = "select count(*) from book where bname like ? or author like ? or ISBN like ?";
        try {
            String searchPattern = "%" + keyword + "%";
            return queryRunner.query(sql, new org.apache.commons.dbutils.handlers.ScalarHandler<Long>(),
                    searchPattern, searchPattern, searchPattern);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }
} 